package com.lantosec.hmrp.modules.form.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.lantosec.hmrp.core.exception.AppException;
import com.lantosec.hmrp.core.kit.Result;
import com.lantosec.hmrp.core.kit.ResultEnum;
import com.lantosec.hmrp.core.util.DateUtils;
import com.lantosec.hmrp.core.util.FlowableUtils;
import com.lantosec.hmrp.core.util.UUIDUtils;
import com.lantosec.hmrp.modules.form.entity.FormColumns;
import com.lantosec.hmrp.modules.form.entity.FormLayout;
import com.lantosec.hmrp.modules.form.entity.FormTable;
import com.lantosec.hmrp.modules.form.enumeration.ColoumnTypeEnum;
import com.lantosec.hmrp.modules.form.enumeration.FormTableDefEnum;
import com.lantosec.hmrp.modules.form.enumeration.SQLTypeEnum;
import com.lantosec.hmrp.modules.form.mapper.FormColumnsMapper;
import com.lantosec.hmrp.modules.form.mapper.FormLayoutMapper;
import com.lantosec.hmrp.modules.form.mapper.FormTableMapper;
import com.lantosec.hmrp.modules.form.service.*;
import com.lantosec.hmrp.modules.form.vo.*;
import com.lantosec.hmrp.modules.sysdata.service.ISysDataSerialNumberService;
import com.lantosec.hmrp.modules.workflow.vo.SaveProcessTaskVO;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;

/**
 * 表单数据
 *
 * @author lichaowei
 * @date 2020/4/22
 */
@Service
@Primary
@Log4j2
public class FormServiceImpl implements IFormService {

    @Autowired
    private IFormLayoutService formLayoutService;

    @Autowired
    private IFormTableService formTableService;

    @Autowired
    private FormColumnsMapper formColumnsMapper;

    @Autowired
    IBindColumnValueService bindColumnValueService;

    @Autowired
    ISysDataSerialNumberService sysDataSerialNumberService;

    /**
     *  默认主键列名
     */
    private final String DEFAULT_PRIMARY_COLUMN = "id";
    /**
     *  默认外键列名
     */
    private final String DEFAULT_FOREIGN_COLUMN = "parent_id";

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Result<String> save(SaveProcessTaskVO saveVO) {
        String layoutId = saveVO.getLayoutId();
        String tableId = saveVO.getFormTableId();
        // 给没有表单Id，则根据表单布局Id去查询
        if (StringUtils.isBlank(tableId)) {
            // 解析表单数据
            if (StringUtils.isBlank(layoutId)) {
                return Result.fail("缺少参数");
            }
            // 根据layoutId查询对应的表单布局
            FormLayout formLayout = formLayoutService.getBaseMapper().selectById(layoutId);
            if (Objects.isNull(formLayout)) {
                return Result.fail("未找到表单布局");
            }
            // 根据table_id查询对应的数据库表
            tableId = formLayout.getTableId();
            saveVO.setFormTableId(tableId);
        }
        FormTable formTable = formTableService.getBaseMapper().selectById(tableId);
        if (Objects.isNull(formTable)) {
            return Result.fail("未找到数据表单");
        }

        // 保存数据到数据库表
        JSONObject jsonObject = JSON.parseObject(saveVO.getFormData());
        // 主表对象转JSON字符串
        // 查询主表主键列信息
        FormColumns primaryColumns = formColumnsMapper.selectOne(new QueryWrapper<FormColumns>()
                .eq(String.valueOf(FormColumns.ColName.primary_key), 1)
                .eq(String.valueOf(FormColumns.ColName.table_id), tableId));
        if (Objects.isNull(primaryColumns)) {
            return Result.fail("未找到数据表单的主键列字段");
        }
        // 获取主表的主键值
        String primaryId = "";
        Object primaryObject = jsonObject.get(com.lantosec.hmrp.core.util.StringUtils.underlineToCamelCase(primaryColumns.getColumnName()));
        // 如果用驼峰形式取不到值，用下划线格式再取一次
        primaryObject = Objects.isNull(primaryObject) ? jsonObject.get(primaryColumns.getColumnName()) : primaryObject;
        if (!Objects.isNull(primaryObject)) {
            primaryId = primaryObject.toString();
        }
        if (StringUtils.isNotEmpty(saveVO.getBusinessKey())) {
            primaryId = saveVO.getBusinessKey();
        }
        // 所有插入、修改、删除的SQL语句列表
        List<String> insertSqlList = new ArrayList<>();
        List<String> updateSqlList = new ArrayList<>();
        List<String> deleteSqlList = new ArrayList<>();

        if (StringUtils.isBlank(primaryId)) {
            // 添加记录相关的SQL，生成主表insert sql语句
            primaryId = UUIDUtils.get();
            String insertSql = generateSql(formTable, jsonObject, primaryId, null, SQLTypeEnum.MASTER_INSERT);
            if (StringUtils.isNotBlank(insertSql)) {
                insertSqlList.add(insertSql);
            }
        } else {
            // 修改记录相关的SQL，生成主表update sql语句
            String updateSql = generateSql(formTable, jsonObject, primaryId, "", SQLTypeEnum.MASTER_UPDATE);
            if (StringUtils.isNotBlank(updateSql)) {
                updateSqlList.add(updateSql);
            }
        }
        // 子表数据集合
            /*Object children=jsonObject.get(childrenKey);
            if(!Objects.isNull(children)) {
                List<FormChildDataVO> childrenDataList = JSON.parseArray(children.toString(), FormChildDataVO.class);
            }*/
        // 生成子表的insert sql语句和delete sql语句
        generateChildSql(primaryId, formTable.getTableId(), jsonObject, insertSqlList, deleteSqlList);

        int sqlCount = insertSqlList.size() + updateSqlList.size();
        int executeCount = 0;
        // 执行sql语句
        deleteSqlList.forEach(item -> formColumnsMapper.executeDeleteSql(item));
        executeCount += updateSqlList.stream().mapToInt(item -> formColumnsMapper.executeUpdateSql(item)).sum();
        executeCount += insertSqlList.stream().mapToInt(item -> formColumnsMapper.executeInsertSql(item)).sum();
        if (executeCount != sqlCount) {
            throw new AppException("保存数据失败");
        }
        return Result.ok(primaryId);
    }

    /**
     * 生成SQL语句
     * @param formTable 表单对象
     * @param jsonData  表单数据
     * @param primaryId 主键ID，生成主/子表SQL时用
     * @param foreignId 外键ID，生成子表SQL时用
     * @return  生成的SQL
     */
    private String generateSql(FormTable formTable, JSONObject jsonData, String primaryId, String foreignId, SQLTypeEnum sqlType) {
        // 所有的列
        List<FormColumns> baseColumnsList = formColumnsMapper.selectList(new QueryWrapper<FormColumns>()
                .eq(String.valueOf(FormColumns.ColName.column_type), ColoumnTypeEnum.BASE_COLUMN.getType())
                .eq(String.valueOf(FormColumns.ColName.table_id), formTable.getTableId()));

        // 需要插入数据库的数据Map对象
        Map<String, String> dataMap = new HashMap<>();
        AtomicReference<String> primaryName = new AtomicReference<>("");
        AtomicReference<String> foreignName = new AtomicReference<>("");

        // 遍历所有的基础列，获取编辑的列名和列名对应的值
        for (FormColumns item : baseColumnsList) {
            String columnName = item.getColumnName();
            // 主键的值
            if (!Objects.isNull(item.getPrimaryKey()) && item.getPrimaryKey()) {
                dataMap.put(columnName, primaryId);
                primaryName.set(columnName);
            }
            // 外键的值
            else if (!Objects.isNull(item.getForeignKey()) && item.getForeignKey()) {
                if (StringUtils.isBlank(foreignId)) {
                    throw new AppException("外键字段" + columnName + "不能为NULL");
                }
                dataMap.put(columnName, foreignId);
                foreignName.set(columnName);
            }
            // 普通的列
            else {
                if (Objects.isNull(jsonData) || jsonData.isEmpty()) {
                    continue;
                }
                // 从json中获取要插入的值
                String extColumnName = "ext";
                if (extColumnName.equals(columnName)) {
                    // 扩展字段的值是特殊处理，解析对象，把扩展字段转json保存到ext字段
                    String extJson = getExtColumnJson(formTable.getTableId(), jsonData);
                    dataMap.put(columnName, extJson);
                } else {
                    // json数据中的名字
                    String jsonName = com.lantosec.hmrp.core.util.StringUtils.underlineToCamelCase(columnName);
                    Object columnValue = jsonData.get(jsonName);
                    // 如果用驼峰形式取不到值，用下划线格式再取一次
                    columnValue = Objects.isNull(columnValue) ? jsonData.get(columnName) : columnValue;
                    if (!Objects.isNull(columnValue)) {
                        dataMap.put(columnName, String.valueOf(convertData(columnValue)));
                    } else {
                        // 不允许为空的字段如果没有值则抛出异常
                        /*String isNullable = item.getIsNullable();
                        if ("NO".equals(isNullable)) {
                            throw new AppException("字段" + columnName + "不能为NULL");
                        }*/
                    }
                }
            }
        }
        // 如果没有主键/外键，则添加默认的字段名
        if (StringUtils.isEmpty(primaryName.get()) && StringUtils.isNotEmpty(primaryId) && !dataMap.containsKey(DEFAULT_PRIMARY_COLUMN)) {
            dataMap.put(DEFAULT_PRIMARY_COLUMN, primaryId);
            primaryName.set(DEFAULT_PRIMARY_COLUMN);
        }
        if (StringUtils.isEmpty(foreignName.get()) && StringUtils.isNotEmpty(foreignId) && !dataMap.containsKey(DEFAULT_FOREIGN_COLUMN)) {
            dataMap.put(DEFAULT_FOREIGN_COLUMN, foreignId);
            foreignName.set(DEFAULT_FOREIGN_COLUMN);
        }

        // 构建SQL
        return createSql(dataMap, formTable.getTableName(), primaryName.get(), primaryId, foreignName.get(), foreignId, sqlType);
    }

    private String createSql(Map<String,String> dataMap, String tableName, String primaryName, String primaryId
            , String foreignName, String foreignId, SQLTypeEnum sqlType) {
        // 构建SQL
        if (dataMap.isEmpty()) {
            return null;
        }
        StringBuilder sql = new StringBuilder();
        if (SQLTypeEnum.MASTER_INSERT.equals(sqlType) || SQLTypeEnum.CHILD_INSERT.equals(sqlType)) {
            // 默认流水号字段列名
            String serialNumberColumn = "serial_number";
            // 如果有流水号字段，自动生成对应规则的流水号
            if (dataMap.containsKey(serialNumberColumn)) {
                // 获取流水号值Id
                String serialNumberId = dataMap.get(serialNumberColumn);
                // 根据类型获取最新的流水号
                String serialNumber = sysDataSerialNumberService.generateSerialNumber(serialNumberId);
                dataMap.put(serialNumberColumn, serialNumber);
            }
            StringBuilder values = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                columns.append(entry.getKey()).append(",");
                values.append("'").append(entry.getValue()).append("',");
            }
            columns.delete(columns.length() - 1, columns.length());
            values.delete(values.length() - 1, values.length());

            sql.append("insert into ").append(tableName);
            sql.append("(").append(columns).append(")");
            sql.append("values").append("(").append(values).append(");");
        } else if (SQLTypeEnum.MASTER_UPDATE.equals(sqlType)) {
            // 主表更改
            sql.append(" update ").append(tableName).append(" set ");
            for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                sql.append(entry.getKey()).append("=").append("'").append(entry.getValue()).append("',");
            }
            sql.delete(sql.length() - 1, sql.length());
            sql.append(" where ").append(primaryName).append("=").append("'").append(primaryId).append("';");
        } else {
            // 根据外键删除
            sql.append("delete from ").append(tableName);
            sql.append(" where ").append(foreignName).append("=").append("'").append(foreignId).append("';");
        }
        return sql.toString();
    }

    /**
     * 批量生成子表的SQL语句
     * @param foreignId         外键ID（主表Id）
     * @param parentTableId     父表表格Id
     * @param jsonObject        数据对象
     * @param insertSqlList     插入SQL
     * @param deleteSqlList     删除SQL
     * @return  生成的子表SQL语句
     */
    private void generateChildSql(String foreignId, String parentTableId, JSONObject jsonObject,
                                  List<String> insertSqlList, List<String> deleteSqlList) {
        List<FormTable> childrenTableList = getChildFormTable(parentTableId);
        if (CollectionUtils.isEmpty(childrenTableList)) {
            return;
        }
        // 因为存在列表中字段的值有时候为NULL或者有默认值，比如createTime这个字段有些记录的值为空，故生成单条插入语句
        childrenTableList.forEach(childTable -> {
            // 子表数据集合
            Object tableData = jsonObject.get(childTable.getTableId());
            if (Objects.isNull(tableData)) {
                return;
            }

            List<Object> dataList = JSON.parseArray(tableData.toString(), Object.class);
            // 子表对象
            if (CollectionUtils.isNotEmpty(dataList)) {
                // 根据外键（主表Id）删除子表记录

                String delSql = generateSql(childTable, null, UUIDUtils.get(), foreignId, SQLTypeEnum.CHILD_DELETE);
                if (StringUtils.isNotBlank(delSql)) {
                    deleteSqlList.add(delSql);
                }

                // 每条记录生成一条SQL
                dataList.forEach(data -> {
                    JSONObject json = (JSONObject) JSONObject.toJSON(data);
                    String sql = generateSql(childTable, json, UUIDUtils.get(), foreignId, SQLTypeEnum.CHILD_INSERT);
                    if (StringUtils.isNotBlank(sql)) {
                        insertSqlList.add(sql);
                    }
                });
            }
        });

    }

    /**
     * 扩展字段转Json字符串
     * @param tableId    表格Id
     * @param jsonObject 提交的json对象
     * @return
     */
    private String getExtColumnJson(String tableId, JSONObject jsonObject) {
        // 查询所有的扩展字段
        List<FormColumns> list = formColumnsMapper.selectList(new QueryWrapper<FormColumns>()
                .eq(String.valueOf(FormColumns.ColName.table_id), tableId)
                .eq(String.valueOf(FormColumns.ColName.column_type), ColoumnTypeEnum.EXT_COLUMN.getType()));

        JSONObject extJson = new JSONObject();
        if (CollectionUtils.isNotEmpty(list)) {
            list.forEach(item -> {
                String columnName = com.lantosec.hmrp.core.util.StringUtils.underlineToCamelCase(item.getColumnName());
                boolean flag = jsonObject.containsKey(columnName);
                if (flag) {
                    extJson.put(columnName, jsonObject.get(columnName));
                }else{
                    flag = jsonObject.containsKey(item.getColumnName());
                    if (flag) {
                        extJson.put(item.getColumnName(), jsonObject.get(item.getColumnName()));
                    }
                }
            });
        }
        if (extJson.size() > 0) {
            return extJson.toJSONString();
        }

        return "";
    }

    @Override
    public Result<FormVO> queryDataByFormId(String layoutId, String dataId) {
        FormVO formVO = new FormVO();
        try {
            // 根据layoutId查询对应的表单布局信息
            FormLayout formLayout = formLayoutService.getBaseMapper().selectById(layoutId);
            if (Objects.isNull(formLayout)) {
                return Result.fail("未找到表单布局", null);
            }
            formVO.setFormLayout(formLayout);

            // 查询数据
            Object formDataVO = selectData(formLayout.getTableId(),dataId);
            formVO.setFormData(formDataVO);
        } catch (Exception ex) {
            log.error("查询表单数据失败。", ex);
        }
        return Result.ok(formVO);
    }

    private List<FormTable> getChildFormTable(String parentId) {
        return formTableService.getBaseMapper().selectList(new QueryWrapper<FormTable>()
                .eq(String.valueOf(FormTable.ColName.table_def), FormTableDefEnum.DETAILED.getType())
                .eq(String.valueOf(FormTable.ColName.parent_id), parentId));
    }

    /**
     * 查询数据
     * @param tableId 表单Id
     * @param primaryId 表单数据Id
     * @return  返回的数据
     */
    @Override
    public JSONObject selectData(String tableId, String primaryId) {
        // 根据table_id查询对应的数据库主表
        FormTable formTable = formTableService.getBaseMapper().selectById(tableId);
        // 主表SQL
        String sql = generateSelectSql(formTable, primaryId, Boolean.TRUE);
        if (StringUtils.isBlank(sql)) {
            return null;
        }
        // 查询主表数据
        List<Map<String, Object>> masterList = formColumnsMapper.executeSelectSql(sql);
        if (CollectionUtils.isEmpty(masterList)) {
            return null;
        }
        JSONObject jsonObject = mapToJson(masterList.get(0), Boolean.TRUE);
        // 查询子表数据
        selectChildTableData(jsonObject, tableId, primaryId);

        return jsonObject;
    }

    /**
     * 查询子表数据
     */
    private void selectChildTableData(JSONObject jsonObject, String tableId, String primaryId) {
        // 根据父表Id查询所有子表表单信息
        List<FormTable> list = getChildFormTable(tableId);
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        // 遍历子表对象
        for (FormTable item : list) {
            List<Object> objectList = new ArrayList<>();
            // 子表查询的SQL
            String childSql = generateSelectSql(item, primaryId, Boolean.FALSE);
            if (StringUtils.isNotBlank(childSql)) {
                try {
                    // 查询子表的数据
                    List<Map<String, Object>> childList = formColumnsMapper.executeSelectSql(childSql);
                    if (CollectionUtils.isNotEmpty(childList)) {
                        childList.forEach(childMap -> {
                            objectList.add(mapToJson(childMap, Boolean.TRUE));
                        });
                    }
                } catch (Exception ex) {
                    log.error("子表数据查询失败：tableName:" + item.getTableName() + "，parentId:" + primaryId, ex);
                }
            }
            jsonObject.put(item.getTableId(), objectList);
        }
    }

    /**
     * 生成查询SQL语句
     * @param formTable     表单对象
     * @param columnValue   查询条件的值
     * @param isPrimary     是否主键查询，不然就是外键查询。 true 是，false 否
     * @return
     */
    private String generateSelectSql(FormTable formTable,String columnValue,Boolean isPrimary) {
        // 查询条件的列名
        FormColumns whereColumn = formColumnsMapper.selectOne(new QueryWrapper<FormColumns>()
                .eq(isPrimary, String.valueOf(FormColumns.ColName.primary_key), 1)
                .eq(!isPrimary, String.valueOf(FormColumns.ColName.foreign_key), 1)
                .eq(String.valueOf(FormColumns.ColName.table_id), formTable.getTableId()));

        String whereColumnName;
        if (!Objects.isNull(whereColumn)) {
            whereColumnName = whereColumn.getColumnName();
        }
        else {
            whereColumnName = isPrimary ? DEFAULT_PRIMARY_COLUMN : DEFAULT_FOREIGN_COLUMN;
        }
        return "select * from " + formTable.getTableName() + " where " + whereColumnName + "=" + "'" + columnValue + "';";
    }

    /**
     * map转Json对象
     *
     * @param map           数据对象
     * @param isToCamelCase 是否转驼峰
     * @return
     */
    private JSONObject mapToJson(Map<String,Object> map, boolean isToCamelCase) {
        JSONObject jsonObject = new JSONObject();

        // 对象转JSON字符串
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String mapKey = entry.getKey();
            Object mapValue = convertData(entry.getValue());
            if(isToCamelCase) {
                // 字段名称下划线转驼峰格式
                jsonObject.put(com.lantosec.hmrp.core.util.StringUtils.underlineToCamelCase(mapKey), mapValue);
            }
            else {
                jsonObject.put(mapKey, mapValue);
            }
        }
        /*JSONObject jsonObject = JSONObject.parseObject(
                JSON.toJSONString(map, SerializerFeature.DisableCircularReferenceDetect, SerializerFeature.WriteDateUseDateFormat));*/

        // 扩展字段转json并合并
        Object extObj = jsonObject.get("ext");
        if (!Objects.isNull(extObj) && StringUtils.isNotBlank(extObj.toString())) {
            String extValue = extObj.toString();
            JSONObject extJson = JSONObject.parseObject(extValue);
            jsonObject.putAll(extJson);
            jsonObject.put("ext", "");
        }
        return jsonObject;
    }

    private Object convertData(Object value) {
        String timeType = "java.sql.Timestamp";
        String boolType = "java.lang.Boolean";
        String dataType = "java.lang.Long";
        if (!Objects.isNull(value)) {
            try {
                if (timeType.equals(value.getClass().getTypeName())) {
                    Date date = (Date) value;
                    value = DateUtils.dateToStr(date, DateUtils.DEFAULTFORMAT);
                } else if (boolType.equals(value.getClass().getTypeName())) {
                    value = Boolean.TRUE.equals(value) ? 1 : 0;
                } else if (dataType.equals(value.getClass().getTypeName())) {
                    value = DateUtils.stampToDate(value.toString());

                }
            } catch (Exception ex) {
            }
        }
        return value;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void writeBackData(String layoutId, String dataId, String userId) {

        // 根据layoutId查询对应的表单布局信息
        FormLayout formLayout = formLayoutService.getBaseMapper().selectById(layoutId);
        if (Objects.isNull(formLayout)) {
            throw new AppException("未找到表单布局");
        }

        // 查询子表数据
        JSONObject jsonObject = new JSONObject();
        selectChildTableData(jsonObject, formLayout.getTableId(), dataId);

        // 查询当前数据库名称
        List<String> tableSchemaList = formColumnsMapper.executeSql("SELECT DATABASE()");
        String tableSchema = tableSchemaList.get(0);

        List<String> updateSqlList = new ArrayList<>();
        // 遍历子表集合
        for (String key : jsonObject.keySet()) {
            // 一个表格Id对应多条数据记录，数据格式：{"tableId":[{"id":1,"name":"张三"},{"id":2,"name":"李四"}]}
            List<JSONObject> dataList = JSON.parseArray(jsonObject.get(key).toString(), JSONObject.class);
            if (CollectionUtils.isNotEmpty(dataList)) {
                // 遍历子表数据集合
                dataList.forEach(item -> {
                    String sql = generateWriteBackSql(item, tableSchema, userId);
                    updateSqlList.add(sql);
                });
            }
        }

        int executeCount = 0;
        // 执行sql语句
        executeCount += updateSqlList.stream().mapToInt(item -> formColumnsMapper.executeUpdateSql(item)).sum();
        if (executeCount != updateSqlList.size()) {
            throw new AppException("数据回写更新SQL执行失败");
        }
    }

    /**
     * 生成子表回写SQL
     * @param item  子表数据JSON
     */
    private String generateWriteBackSql(JSONObject item, String tableSchema, String userId) {
        if (Objects.isNull(item)) return null;
        // 根据子表的回写表名（write_back_table），记录Id（write_back_id）回写审核的状态（write_back_status）和议论（write_back_comment）
        String backTableName = (String) item.get("writeBackTable");
        String backDataId = (String) item.get("writeBackId");
        String backStatus = (String) item.get("writeBackStatus");
        String backComment = (String) item.get("writeBackComment");
        String backProInsId = (String) item.get("writeBackProInsId");
        // 如果用驼峰形式取不到值，用下划线格式再取一次
        backTableName = StringUtils.isEmpty(backTableName) ? (String) item.get("write_back_table") : backTableName;
        backDataId = StringUtils.isEmpty(backDataId) ? (String) item.get("write_back_id") : backDataId;
        backStatus = StringUtils.isEmpty(backStatus) ? (String) item.get("write_back_status") : backStatus;
        backComment = StringUtils.isEmpty(backComment) ? (String) item.get("write_back_comment") : backComment;
        backProInsId = StringUtils.isEmpty(backProInsId) ? (String) item.get("write_back_pro_ins_id") : backProInsId;
        if(StringUtils.isEmpty(backTableName)){
            throw new AppException("回写表的表名不能为空");
        }
        if(StringUtils.isEmpty(backDataId)){
            throw new AppException("回写表的主键值不能为空");
        }
        // 查询表的主键字段，根据主键字段作为where条件
        List<FormColumnsVO> columnsVOList = formColumnsMapper.queryByTableInfo(tableSchema, backTableName);
        String priColumnName = columnsVOList.stream().filter(a -> "PRI".equals(a.getColumnKey())).findFirst().orElse(new FormColumnsVO()).getColumnName();
        if (StringUtils.isEmpty(priColumnName)) {
            priColumnName = DEFAULT_PRIMARY_COLUMN;
        }

        // 回写SQL，回写表固定字段 result_status，result_comment，result_pro_ins_id
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(tableSchema).append(".").append(backTableName);
        sql.append(" set result_status='").append(backStatus).append("'");
        sql.append(",result_comment='").append(backComment).append("'");
        if(StringUtils.isNotEmpty(backProInsId)){
            sql.append(",result_pro_ins_id='").append(backProInsId).append("'");
        }
        sql.append(" where ").append(priColumnName).append("='").append(backDataId).append("'");
        return sql.toString();
    }

    @Override
    public Result<Object> calculateValue(CalculateValueVO vo) {
        if (Objects.isNull(vo.getFormData()) || StringUtils.isBlank(vo.getValueType()) || StringUtils.isBlank(vo.getValueData())) {
            return Result.fail("缺少参数");
        }
        JSONObject jsonObject = JSON.parseObject(vo.getFormData());
        Object result = bindColumnValueService.getBindValue(vo.getValueType(), vo.getValueData(), jsonObject);
        return Result.ok(result);
    }

    /**
     * 辅助决策
     *
     */
    @Override
    public Result<DecisionSupportResultVO> decisionSupport(DecisionSupportVO entity) {
        // 查询业务数据和表单布局信息
        Result<FormVO> formResult = this.queryDataByFormId(entity.getLayoutId(), entity.getDataId());
        if (!ResultEnum.SUCCESS.code.equals(formResult.getState())) {
            throw new AppException("查询表单数据失败");
        }
        // 流程表单主表,子表数据
        JSONObject jsonObject = (JSONObject) formResult.getData().getFormData();

        // 根据决策数据子表表格Id 查询辅助决策数据
        JSONArray jsonArray = jsonObject.getJSONArray(entity.getChildTableId());
        if (CollectionUtils.isEmpty(jsonArray)) {
            throw new AppException("没有需要辅助决策的数据");
        }

        String COMPARISON_MESSAGE = "comparisonMessage";
        // 处理后的辅助决策数据
        List<JSONObject> resultDataList = new ArrayList<>();
        // 处理后的辅助决策数据消息，可以遍历生成提示：第N行有count个错误，分别是aa，bb，cc；
        //Map<Integer, List<String>> resultMessageMap = new HashMap<>();
        // 辅助决策通过的数量
        int successCount = 0;
        // 辅助决策不通过的数量
        int failCount = 0;
        // 遍历辅助决策数据集合,根据比较类型执行表达式或者SQL,把比较结果记录到对应的结果字段
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject item = (JSONObject) jsonArray.get(i);

            // 辅助决策验证
            List<String> messageList = this.comparison(item, entity.getCondition());

            if (CollectionUtils.isEmpty(messageList)) {
                successCount ++;
            } else {
                failCount ++;
                item.put(COMPARISON_MESSAGE, StringUtils.join(messageList, ","));
            }

            resultDataList.add(item);
            //resultMessageMap.put(i, messageList);
        }

        // 统计辅助决策数据，替换提示模板内容
        JSONObject countJson = new JSONObject();
        countJson.put("successCount", successCount);
        countJson.put("failCount", failCount);
        String promptMessage = com.lantosec.hmrp.core.util.StringUtils.processTemplate(entity.getPromptTemplate(), countJson);

        // 返回辅助决策数据集合
        DecisionSupportResultVO resultVO = new DecisionSupportResultVO();
        resultVO.setDecisionData(resultDataList);
        resultVO.setPromptMessage(promptMessage);

        return Result.ok(resultVO);
    }

    private List<String> comparison(JSONObject jsonObject, List<DecisionSupportComparisonVO> list) {
        List<String> messageList = new ArrayList<>();

        for (int j = 0; j < list.size(); j++) {
            DecisionSupportComparisonVO comparisonVO = list.get(j);

            boolean flag = true;
            if ("SQL".equals(comparisonVO.getComparisonType())) {
                // 替换文本的字段，默认取当前对象的参数，用${field}
                String parameter = com.lantosec.hmrp.core.util.StringUtils.processTemplate(comparisonVO.getComparisonValue(), jsonObject);
                if (!StringUtils.isNotBlank(parameter)) {
                    continue;
                }
                // 执行SQL
                List<Map<String, Object>> resultList = formColumnsMapper.executeSelectSql(parameter);
                if (CollectionUtils.isNotEmpty(resultList)) {
                    flag = false;
                }
            } else {
                // 表达式比较
                // 下划线转驼峰，避免输入的下划线的参数名称跟查询的参数名称不匹配
                String comparisonValue = com.lantosec.hmrp.core.util.StringUtils.underlineToCamelCase(comparisonVO.getComparisonValue());
                String expression = "${" + comparisonValue + "}";
                Map<String, Object> conditionMap = JSONObject.parseObject(jsonObject.toString());
                flag = FlowableUtils.isCondition(expression, conditionMap);
            }

            // 验证失败则添加一条警告信息
            if (!flag) {
                messageList.add(comparisonVO.getWarningTemplate());
            }
        }

        return messageList;
    }
}
